﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Collections.Specialized;
using System.IO;
using System.Linq;
using System.Windows;
using System.Windows.Controls;
using BindableDataGrid.Data;
using Combinatorics;
using ExcelDataReader;
using ExcelDataReader.Silverlight;
using ExcelDataReader.Silverlight.Data;

namespace SilveR
{
    public class DataHandler
    {
        //the data, as datatable
        private DataTable dataTable = null;
        public DataTable DataTable
        {
            get { return dataTable; }
        }

        //handle on the file iteself as fileinfo
        private FileInfo selectedFile = null;
        public FileInfo SelectedFile
        {
            get { return selectedFile; }
        }

        //an int, that can be used to change the displayed filename
        private int filenameSuffix = 0;
        public int FilenameSuffix
        {
            get { return filenameSuffix; }
            set { filenameSuffix = value; }
        }

        //the string that is used in comboboxes and headers etc, can be modified through changing the filename suffix
        public string DisplayFileName
        {
            get
            {
                string displayFileName = selectedFile.Name;
                if (filenameSuffix > 0) // then add the number onto the end of the file, in brackets
                {
                    displayFileName = displayFileName + " (" + filenameSuffix.ToString() + ")";
                }

                return displayFileName;
            }
        }

        public DataHandler() { } //ctor

        //return the variable list for the data (as observable collection so can use in combo boxes etc)
        public ObservableCollection<ColumnInfo> GetVariableList()
        {
            SortableObservableCollection<ColumnInfo> variableList = new SortableObservableCollection<ColumnInfo>();
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                ColumnInfo ci = new ColumnInfo(i, dataTable.Columns[i].ColumnName);
                variableList.Add(ci);
            }

            variableList.Sort(new ColumnIndexComparer());
            return variableList;
        }

        public delegate void LoadFileCompletedEventHandler(object sender, EventArgs e);
        public event LoadFileCompletedEventHandler LoadFileCompleted;

        public void LoadFile()
        {
            //ask the user to select their file...
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel or CSV Files|*.xls;*.xlsx;*.csv";
            bool? userClickedOK = ofd.ShowDialog();

            dataTable = null;
            selectedFile = ofd.File;
            try
            {
                if (userClickedOK == true)
                {
                    if (selectedFile.Extension.ToUpper() == ".CSV") //no necessary as already filtering....
                    {
                        //use the CSVReader to read in the data
                        using (CSVReader csv = new CSVReader(selectedFile.OpenRead()))
                        {
                            dataTable = ReadInCSVData(csv);
                            CheckDataTable();
                        }
                    }
                    else if (selectedFile.Extension.ToUpper() == ".XLS" || selectedFile.Extension.ToUpper() == ".XLSX")
                    {
                        //read in xls file
                        FileStream stream = ofd.File.OpenRead();

                        IExcelDataReader excelReader = null;

                        if (selectedFile.Extension.ToUpper() == ".XLS")
                        {
                            // Reading from a binary Excel file ('97-2003 format; *.xls)
                            excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                        }
                        else if (selectedFile.Extension.ToUpper() == ".XLSX")
                        {
                            // Reading from a OpenXml Excel file (2007 format; *.xlsx)
                            excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                        }

                        excelReader.IsFirstRowAsColumnNames = true; //added on recommendation from excel reader discussion group

                        // The result of each spreadsheet will be created in the result.Tables
                        excelReader.WorkBookFactory = new ExcelWorkBookFactory();
                        IWorkBook excelWorkBook = excelReader.AsWorkBook();

                        stream.Close();
                        excelReader.Close();

                        //if there is more than one worksheet then ask the user which sheet they want, otherwise automatically load it
                        if (excelWorkBook.WorkSheets.Count == 0)
                        {
                            MessageBox.Show("Error reading file. No sheets with valid data could be found in the Excel workbook.", "Load DataSet", MessageBoxButton.OK);
                        }
                        else if (excelWorkBook.WorkSheets.Count == 1)
                        {
                            List<IWorkSheet> workSheets = (List<IWorkSheet>)excelWorkBook.WorkSheets;
                            FillDataTableFromExcelSheet(workSheets[0]);
                            CheckDataTable();
                        }
                        else
                        {
                            ExcelSheetSelectionWindow frm = new ExcelSheetSelectionWindow(excelWorkBook);
                            frm.Closed += new EventHandler(frm_Closed);
                            frm.Show();
                        }
                    }
                }
            }
            catch (IOException) //move  this catch block
            {
                MessageBox.Show("Error reading file. The file is either corrupted or its file extension is inconsistent with its contents.", "Load DataSet",
                                MessageBoxButton.OK);
                dataTable = null;
            }
        }

        private void frm_Closed(object sender, EventArgs e)
        {
            ExcelSheetSelectionWindow frm = (ExcelSheetSelectionWindow)sender;

            if (frm.DialogResult == true)
            {
                FillDataTableFromExcelSheet(frm.SelectedWorkSheet);

                if (dataTable != null)
                {
                    CheckDataTable();
                }
            }
        }

        private void CheckDataTable()
        {
            //need to check here that no taboo characters are in the dataset
            foreach (DataColumn col in dataTable.Columns)
            {
                bool illegalCharFound = false;
                string illegalCharMessage = null;

                //check column headers first...
                foreach (string s in App.tabooColumnCharList)
                {
                    if (col.ColumnName.Contains(s))
                    {
                        illegalCharFound = true;
                        illegalCharMessage = "The dataset contains characters in the column headers that we cannot handle (such as + * ` ~ ,)";
                        break;
                    }
                }

                if (!illegalCharFound)
                {
                    foreach (DataRow row in dataTable.Rows)
                    {
                        foreach (string s in App.tabooDataCharList)
                        {

                            if (row[col.ColumnName] != null && row[col.ColumnName].ToString().Contains(s))
                            {
                                illegalCharFound = true;
                                illegalCharMessage = "The dataset contains characters in the main body of the data that we cannot handle (such as ` ,)";
                                break;
                            }
                        }
                    }
                }

                if (illegalCharFound)
                {
                    MessageBox.Show(illegalCharMessage + Environment.NewLine +
                            "You will need to remove any of these characters from the dataset and reimport it into InVivoStat.", "Check Import", MessageBoxButton.OK);
                    dataTable = null;
                    selectedFile = null;
                    return;
                }
            }

            //now have finished checking can raise event
            if (LoadFileCompleted != null)
                LoadFileCompleted(this, new EventArgs());
        }

        private void FillDataTableFromExcelSheet(IWorkSheet selectedSheet)
        {
            dataTable = new DataTable("Data");

            //first ensure that all the column names are valid
            int colNo = 1;
            foreach (var col in selectedSheet.Columns)
            {
                if (String.IsNullOrEmpty(col.ColumnName)) col.ColumnName = "Column" + colNo;
                colNo++;
            }

            //add the column names from the excel sheet
            foreach (var column in selectedSheet.Columns)
            {
                //as we are adding in the new columns make sure that they don't already exist in the spreadsheet
                foreach (DataColumn col in dataTable.Columns)
                {
                    if (column.ColumnName == col.ColumnName) //then already exists so error
                    {
                        MessageBox.Show("Error: Two or more of the variables have the same name. Please rename the variables prior importing the data into InVivoStat.",
                        "Load DataSet", MessageBoxButton.OK);
                        dataTable = null;
                        selectedFile = null;
                        return;
                    }
                }

                dataTable.Columns.Add(new DataColumn(column.ColumnName));
            }

            //now add in the data from the excel sheet
            foreach (var row in selectedSheet.Rows)
            {
                DataRow newRow = new DataRow();

                for (int i = 0; i < selectedSheet.Columns.Count; i++)
                {
                    newRow.Items.Add(selectedSheet.Columns[i].ColumnName, row.Values[i]);
                }

                dataTable.Rows.Add(newRow);
            }


            //check no values are null - if they are convert to string.empty
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                foreach (var col in dataTable.Columns)
                {
                    if (dataTable.Rows[i][col.ColumnName] == null) dataTable.Rows[i][col.ColumnName] = String.Empty;
                }
            }
        }

        private DataTable ReadInCSVData(CSVReader csv)
        {
            bool firstLine = true;

            List<string> headers = new List<string>();
            DataTable dataTable = new DataTable("Data");

            try
            {
                //use the csvreader to read in the csv data
                string[] fields;
                while ((fields = csv.GetCSVLine()) != null)
                {
                    if (firstLine) //the first row will always be treated as column headers
                    {
                        for (int i = 0; i < fields.Length; i++)
                        {
                            headers.Add(fields[i]);
                            dataTable.Columns.Add(new DataColumn(fields[i]));
                        }

                        firstLine = false;
                    }
                    else
                    {
                        DataRow row = new DataRow();

                        for (int i = 0; i < fields.Length; i++)
                        {
                            row[headers[i]] = fields[i];
                        }

                        dataTable.Rows.Add(row);
                    }
                }
            }
            catch (ArgumentException)
            {
                dataTable = null;
                MessageBox.Show("The data needs to be in format.", "Error pasting in data.", MessageBoxButton.OK);
                return null;
            }

            return dataTable;
        }

        public List<string> GetLevels(string column)
        {
            //Get a list of the distinct levels in a selected column
            List<string> distinctlevels = new List<string>();

            if (String.IsNullOrEmpty(column) || this.SelectedFile == null) return distinctlevels; //i.e. empty list

            List<string> levels = new List<string>();

            foreach (DataRow row in this.dataTable.Rows)
            {
                levels.Add(row[column].ToString());
            }

            IEnumerable<string> levelsAsIEnumerable = levels.Distinct();

            return levelsAsIEnumerable.ToList();
        }
    }

    public class SortableObservableCollection<T> : ObservableCollection<T>
    {
        // This will use Comparer<T>.Default
        public void Sort()
        {
            this.Sort(0, Count, null);
        }

        // Pass custom comparer to Sort method
        public void Sort(IComparer<T> comparer)
        {
            this.Sort(0, Count, comparer);
        }

        // Use this method to sort part of a collection
        public void Sort(int index, int count, IComparer<T> comparer)
        {
            (Items as List<T>).Sort(index, count, comparer);

            OnCollectionChanged(new NotifyCollectionChangedEventArgs(NotifyCollectionChangedAction.Reset));
        }
    }


    public class ColumnIndexComparer : System.Collections.Generic.IComparer<ColumnInfo>
    {
        public int Compare(ColumnInfo first, ColumnInfo second)
        {
            if (first == null & second == null) { return 0; }
            else if (first == null) { return -1; }
            else if (second == null) { return 1; }
            else if (first.ColumnIndex < second.ColumnIndex) { return -1; }
            else if (first.ColumnIndex == second.ColumnIndex) { return 0; }
            else
            {
                return 1;
            }
        }
    }

    public class ColumnInfo
    {
        public int ColumnIndex { get; private set; }
        public string ColumnName { get; private set; }

        public ColumnInfo(int columnIndex, string columnName)
        {
            ColumnIndex = columnIndex;
            ColumnName = columnName;
        }
    }
}